﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DUserState
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into UserState(UserStateId,UserPassportId,Content,DateCreated) values (@UserStateId,@UserPassportId,@Content,@DateCreated)";

        private const string SQL_UPDATE = "update UserState set UserPassportId=@UserPassportId,Content=@Content,DateCreated=@DateCreated where UserStateId=@UserStateId";

        private const string SQL_DELETE_ID = "delete from UserState where UserStateId=@UserStateId";

        private const string SQL_SELECT_USERID = "select UserStateId,UserPassportId,Content,DateCreated from UserState where UserPassportId=@UserPassportId";

        private const string SQL_SELECT_ID = "select UserStateId,UserPassportId,Content,DateCreated from UserState where UserStateId=@UserStateId";

        #endregion

        #region 公共接口

        public bool Insert(UserStateInfo userStateInfo)
        {
            if (userStateInfo == null)
                throw new ArgumentNullException("userStateInfo");

            SqlParameter[] parms = GetInsertParms(userStateInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        public bool Update(UserStateInfo userStateInfo)
        {
            if (userStateInfo == null || userStateInfo.UserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("userStateInfo");

            SqlParameter[] parms = GetInsertParms(userStateInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userStatId"></param>
        /// <returns></returns>
        public int Delete(long userStatId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserStateId",SqlDbType.BigInt)
                };
            parms[0].Value = userStatId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_ID, parms);

        }

        public UserStateInfo GetUserStateInfo(long userStatId)
        {
            UserStateInfo userStateInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@UserStateId",SqlDbType.BigInt)
                };
            parms[0].Value = userStatId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, parms))
            {
                while (reader.Read())
                {
                    userStateInfo = GetInstanceByDataReader(reader);

                }
            }
            return userStateInfo;
        }

       

        public List<UserStateInfo> GetAllUserStatByUserId(long userId)
        {
            List<UserStateInfo> stats = new List<UserStateInfo>();

            UserStateInfo uerStateInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@PaperId",SqlDbType.BigInt)
                };
            parms[0].Value = userId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERID, parms))
            {
                while (reader.Read())
                {
                    uerStateInfo = GetInstanceByDataReader(reader);
                    if (uerStateInfo != null && uerStateInfo.UserPassportId != 0)
                        stats.Add(uerStateInfo);
                }
            }
            return stats;
        }

        #endregion

        #region 私有接口

        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(UserStateInfo userStateInfo)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserStateId", SqlDbType.BigInt,8),
					new SqlParameter("@UserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@Content", SqlDbType.NVarChar,100),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = userStateInfo.UserStateId;
            parameters[1].Value = userStateInfo.UserPassportId;
            parameters[2].Value = userStateInfo.Content;
            parameters[3].Value = userStateInfo.DateCreated;
            return SetInsertParmsCheck(parameters);
        }
        private UserStateInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            UserStateInfo current = new UserStateInfo();
            //UserStateId,UserPassportId,Content,DateCreated
            current.UserStateId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            current.UserPassportId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            current.Content = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
            current.DateCreated = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);

            return current;
        }
        #endregion
    }
}
